Imports System.Data.OracleClient
Namespace persistent

    Public Class ListModifiedData

        Public Shared Function GetModifiedData() As DataTable


            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            con.Open()
            Dim cmd As New OracleCommand
            cmd.Connection = con
            cmd.CommandText = " eu_bill.pkg_iid_requests.list_modified_data"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("p_list", OracleType.Cursor).Direction = ParameterDirection.Output

            Dim rdr As OracleDataReader
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Dim dt As New DataTable

            dt.Columns.Add("co_id")
            dt.Columns.Add("op_code")
            dt.Columns.Add("dealer")
            dt.Columns.Add("imei")
            dt.Columns.Add("msisdn")
            dt.Columns.Add("sncode")
            dt.Columns.Add("process_id")
            dt.Columns.Add("seq_no")
            dt.Columns.Add("iid_request_id")
            dt.Columns.Add("retry_count")
            dt.Columns.Add("rpe_pack_id")

            Dim str(10) As String

            While rdr.Read
                str(0) = rdr.Item(0)
                str(1) = rdr.Item(1)
                str(2) = rdr.Item(2)
                str(3) = rdr.Item(3)
                str(4) = rdr.Item(4)
                str(5) = rdr.Item(5)
                str(6) = rdr.Item(6)
                str(7) = rdr.Item(7)
                str(8) = rdr.Item(8)
                str(9) = rdr.Item(9)
                If Not IsDBNull(rdr.Item(10)) Then
                    str(10) = rdr.Item(10)
                Else
                    str(10) = ""
                End If
                dt.Rows.Add(str)
            End While

            Return dt

            con.Close()

        End Function

        Public Shared Function UpdateMasterData(ByVal Imei As String, ByVal Dealer As String, ByVal ProcessId As String, ByRef resultMessage As String) As Boolean
            Dim result1 As Boolean = False

            Try
                Dim query As String = "update eu_bill.service_handler_master_data md set imei = '" & Imei & "', Dealer = '" & Dealer & "' where md.process_id = " & ProcessId
                result1 = persistent.myDBConn.ORAExecute(String.Format(query, Imei, Dealer)) > 0

                If result1 Then
                    resultMessage = "Update islemi basarili."
                Else
                    resultMessage = " Update isleminde hata olustu!"
                End If

            Catch ex As Exception
                resultMessage = ex.Message
            End Try

            Return result1

        End Function

        Public Shared Function CheckErrorCode(ByVal ProcessId As String, ByRef resultMessage As String) As Boolean
            Dim result1 As Boolean = False

            Try
                Dim query As String = "select * from eu_bill.iid_requests where process_id = {0} and (status > 0 and status != 10)"
                result1 = persistent.myDBConn.ORAExecute(String.Format(ProcessId)) > 0

                If Not result1 Then
                    resultMessage = "Request'inin olusturulamamistir. Daha Sonra tekrar denenecektir..."
                Else
                    resultMessage = " Request'iniz basarili olarak olusmustur..."
                End If

            Catch ex As Exception
                resultMessage = ex.Message
            End Try

            Return result1

        End Function
        Public Shared Function SendRequest(ByVal processId As String, ByRef status As String, ByRef resultMessage As String) As Boolean

            Try
                persistent.myDBConn.ORAProcedure("eu_bill.pkg_iid_requests.send_request", _
                            New String() {"p_process_id", "p_status"}, New String() {Convert.ToInt32(processId), Convert.ToInt32(status)})

                Return True

            Catch ex As Exception
                resultMessage = ex.Message
            End Try
            Return False


        End Function
    End Class

End Namespace

